#!/usr/bin/php

<?php
include "db_con.php";

$query = "	UPDATE update_state SET is_updating = 1;";

mysql_query($query);

$query = "	SELECT database_name AS name, records_id AS ID
		FROM new_submissions;";
			
$result = mysql_query($query);

while ($row = mysql_fetch_array($result))
{
	$query = "	SELECT has_started AS start, has_finished AS end
			FROM honeyviz_" . $row['name'] . ".submissions_state
			LIMIT 1;";
	
	$tmp = mysql_query($query);
	
	$state = mysql_fetch_array($tmp);
	
	if ($state['start'] == 1 && $state['end'] == 1)
	{
		$query = "	INSERT IGNORE INTO category(category_id, parent_id, event_name, local_port)
				SELECT NULL,
				(
					SELECT category_id
					FROM category
					WHERE event_name = PAR.event_name
				),
				CAT.event_name,
				CAT.local_port
				FROM honeyviz_" . $row['name'] . ".category AS CAT 
					INNER JOIN honeyviz_" . $row['name'] . ".category AS PAR ON PAR.category_id = CAT.parent_id
					WHERE PAR.category_id>6;";
						
		mysql_query($query);
		
		$query = "	INSERT INTO sensor_record(records_id, category_id, location_id, period, quantity) 
				SELECT " . $row['ID'] . ", 
				(
					SELECT category_id
					FROM category
					WHERE event_name = CAT.event_name
				),
				CE.location_id,
				CE.period,
				CE.quantity
				FROM honeyviz_" . $row['name'] . ".cached_events AS CE
					INNER JOIN honeyviz_" . $row['name'] . ".category AS CAT ON CAT.category_id = CE.category_id;";
						
		mysql_query($query);

		$query = "	DELETE FROM new_submissions WHERE database_name = '" . $row['name'] . "';";
		
		mysql_query($query);

		$query = "	DROP DATABASE honeyviz_" . $row['name'] . ";";

		mysql_query($query);

		$query = "	UPDATE update_state SET has_changes = 1;";

		mysql_query($query);

		$query = "	SELECT COUNT(*) AS NUM FROM new_submissions;";

		$tmpResult = mysql_query($query);

		$tmpRow = mysql_fetch_array($tmpResult);

		if ($tmpRow['NUM'] == 0)
		{
			$query = "	TRUNCATE new_submissions;";
			mysql_query($query);
		}
	}
}

$query = "	SELECT has_changes as has_change
		FROM update_state 
		LIMIT 1;";
			
$result = mysql_query($query);

$row = mysql_fetch_array($result);

if ($row['has_change'] == 1)
{
	$query = "	INSERT INTO cached_events(category_id, location_id, period, quantity) 
			SELECT SR.category_id,
			SR.location_id,
			SR.period,
			SR.quantity
			FROM sensor_record AS SR
				INNER JOIN records AS R ON R.records_id = SR.records_id
			WHERE R.is_active = 1
			ON DUPLICATE KEY UPDATE
				quantity = cached_events.quantity + SR.quantity;";
					
	
	mysql_query($query);
}

$query = "	UPDATE update_state SET is_updating = 0, has_changes = 0, has_submissions = 0;";

mysql_query($query);

?>

